package opdr3a;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;


public class Console2 {
	private Connection connection;
	
	public Console2() {
		connectionSetup();
		try {
			//opdr 3b
//			selectKlant(376,2012,5);
			
			//opdr 3c
			commitBetaling("000655139558", 2012, "MAY", getFactuur(5, 2012, "MAY"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	

	/**
	 * Select de sms en bel gegevens van de klant en voer vervolgense de procedure uit.
	 * @param klant_id
	 * @param jaar
	 * @param maand
	 * @throws SQLException
	 */
	private void selectKlant(int klant_id, int jaar, int maand) throws SQLException {
		int aantal_sms = countSMS(klant_id, jaar, maand);
		int aantal_gesprekken = countGesprekken(klant_id, jaar, maand);
		int mobiel_nr = selectTelefoon(klant_id);
		System.out.println("Klant_id: " + klant_id);
		System.out.println("Aantal keer ge-sms't: " + aantal_sms);
		System.out.println("Aantal gesprekken: " + aantal_gesprekken);
		callProcedure(convertMonth(maand), jaar, mobiel_nr);
	}
	
	/**
	 * Voert de procedure van de database uit
	 * @param maand
	 * @param jaar
	 * @param mobiel_nr
	 * @throws SQLException
	 */
	private void callProcedure(String maand, int jaar, int mobiel_nr) throws SQLException {
		CallableStatement cstmt = connection.prepareCall("call ADD_FACTUUR(?,?,?)");
		cstmt.setString(1, maand);
		cstmt.setInt(2, jaar);
		cstmt.setInt(3, mobiel_nr);
		cstmt.execute();
	}
	
	/**
	 * Converteer het nummer van de maand naar de verkorte naam
	 * @param maand_nr
	 * @return Maand naam (verkort)
	 */
	private String convertMonth(int maand_nr) {
		Calendar calendar = Calendar.getInstance();
		calendar.set(Calendar.MONTH, maand_nr);
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("MMM");
		simpleDateFormat.setCalendar(calendar);
		String maand_naam = simpleDateFormat.format(calendar.getTime());
		return maand_naam;
	}
	
	/**
	 * Selecteer het telefoonnummer van een klant
	 * @param klant_id
	 * @return
	 * @throws SQLException
	 */
	private int selectTelefoon(int klant_id) throws SQLException {
		String sql = "SELECT mobiel_nr " +
				"FROM klantgegevens " + 
				"WHERE klant_id = " + klant_id;
		Statement stmt = connection.createStatement();
		stmt.execute(sql);
		ResultSet rs = stmt.getResultSet();
		rs.next();
		int nummer = rs.getInt(1);
		stmt.close();
		System.out.println(sql);
		return nummer;
	}
	
	/**
	 * Tel het aantal gesprekken dat een klant heeft gevoerd.
	 * @param klant_id
	 * @param jaar
	 * @param maand
	 * @return
	 * @throws SQLException
	 */
	private int countGesprekken(int klant_id, int jaar, int maand) throws SQLException {
		String sql = "SELECT COUNT(gesprekken.gesprek_id) AS aantal_sms " +
		"FROM klantgegevens, gesprekken " + 
		"WHERE gesprekken.klant_id = klantgegevens.klant_id " + 
		"AND klantgegevens.klant_id = 329 " +
		"AND EXTRACT(month FROM gesprekken.gesprek_start) = " + maand + " " +
		"AND EXTRACT(year FROM gesprekken.gesprek_start) =" + jaar;
		Statement stmt = connection.createStatement();
		stmt.execute(sql);
		ResultSet rs = stmt.getResultSet();
		rs.next();
		int aantal_sms = rs.getInt(1);
		stmt.close();
		System.out.println(sql);
		return aantal_sms;
	}
	
	/**
	 * Tel het aantal sms berichten dat een klant heeft verstuurd.
	 * @param klant_id
	 * @param jaar
	 * @param maand
	 * @return
	 * @throws SQLException
	 */
	private int countSMS(int klant_id, int jaar, int maand) throws SQLException {
		String sql = "SELECT COUNT(sms_bericht.bericht_id) AS smscount  " +
				"FROM klantgegevens, sms_bericht, abonnement " +
				"WHERE sms_bericht.klant_id = klantgegevens.klant_id " +
				"AND klantgegevens.klant_id = " + klant_id + " " +
				"AND klantgegevens.abonnement_id = abonnement.abonnement_id " +
				"AND EXTRACT(month FROM sms_bericht.verstuur_datetime) = " + maand + " " +
				"AND EXTRACT(year FROM sms_bericht.verstuur_datetime) = " + jaar;
		System.out.println(sql);
		Statement stmt = connection.createStatement();
		stmt.execute(sql);
		ResultSet rs = stmt.getResultSet();
		rs.next();
		int aantal_sms = rs.getInt(1);
		stmt.close();
		System.out.println(sql);
		return aantal_sms;
	}

	/**
	 * Maak verbinding met de database
	 */
	private void connectionSetup() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		try {
			connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "tthr", "oracle");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Haal het te betalen bedrag op van een factuur aan de hand van de parameters.
	 * @param klant_id
	 * @param jaar
	 * @param maand
	 * @return bedrag
	 * @throws SQLException
	 */
	private double getFactuur(int klantID, int jaar, String maand) throws SQLException {
		Statement stmt = connection.createStatement();
		String sql = "SELECT bedrag FROM factuur WHERE klant_id=" + klantID +
					 " AND maand='" + maand + "'"+
					 " AND jaar=" + jaar;
		stmt.execute(sql);
		
		ResultSet result = stmt.getResultSet();
		result.next();
		double bedrag = result.getDouble(1);
		stmt.close();
		return bedrag;
	}
	
	/**
	 * De status van het bedrag op de factuur wordt op betaald gezet
	 * en tegelijktijd wordt hetzelfde bedrag van de rekening afgeschreven.
	 * @param rekeningnummer
	 * @param bedrag
	 * @throws SQLException
	 */
	private void commitBetaling(String rekeningNummer, int jaar, String maand, double bedrag) throws SQLException {
		connection.setAutoCommit(false);
		Statement stmt = connection.createStatement();
		String sqlstatus =  "UPDATE factuur SET status='betaald'" +
							"WHERE klant_id=" +
							"(SELECT klant_id FROM klantgegevens WHERE rekening_nr='" + rekeningNummer +"')" + 
							 " AND maand='" + maand + "'"+
							 " AND jaar=" + jaar;
		stmt.executeUpdate(sqlstatus);

		String sqlbank = "UPDATE bank SET saldo=saldo-" + bedrag +
						 " WHERE bank.rekening_nr="+ rekeningNummer;
		stmt.executeUpdate(sqlbank);

		stmt.close();
		connection.commit();
		connection.setAutoCommit(true);
	}
}